package org.weso.lodin.dao;

import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.weso.lodin.exceptions.LodinModelException;
import org.weso.lodin.to.DatasetTO;
import org.weso.lodin.to.ProviderTO;
import org.weso.lodin.utils.POIUtils;

public class DataInformationIndicatorDAOImpl implements IndicatorDAO{

	private static final String HTTP_WWW_WEBFOUNDATION_ORG = "http://www.webfoundation.org/";
	public static final String SECONDARY_INDICATORS_SHEET_NAME = "SecondaryIndicatorsMetadata";//from DataInformation-180512.xls
	public static final String SECONDARY = "Secondary";
	public static final String PRIMARY_INDICATORS_SHEET_NAME = "FIXME"; //DataInformation-180512.xls
	public static final String PRIMARY = "Primary";

	List<DatasetTO> primaryIndicatorTOs;
	List<DatasetTO> secondaryIndicatorTOs;

	public DataInformationIndicatorDAOImpl(){
		this.primaryIndicatorTOs =  new LinkedList<DatasetTO>();
		this.secondaryIndicatorTOs =  new LinkedList<DatasetTO>();
	}
	public DataInformationIndicatorDAOImpl(InputStream data){
		HSSFWorkbook wb;
		try {
			wb = new HSSFWorkbook(data);
			this.primaryIndicatorTOs = loadPrimaryIndicators(wb);
			this.secondaryIndicatorTOs = loadSecondaryIndicators(wb);
		} catch (IOException e) {
			throw new LodinModelException(e, "Reading HSSFWorkbook");
		}	

	}

	private List<DatasetTO> loadPrimaryIndicators(HSSFWorkbook wb) {
		HSSFSheet sheet = wb.getSheet(PRIMARY_INDICATORS_SHEET_NAME);
		FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
		int rows = sheet.getPhysicalNumberOfRows();
		List<DatasetTO> indicatorTOs  = new LinkedList<DatasetTO>();
		for (int r = 2; r < rows+1; r++) {//FIXME: Why +1?
			HSSFRow row = sheet.getRow(r);
			if (row == null) {
				continue;
			}
			int cells = row.getPhysicalNumberOfCells();		
			if(cells <= 10){
				DatasetTO currentIndicator = new DatasetTO();
				currentIndicator.setLayer(POIUtils.extractValue(row.getCell(0), evaluator));
				currentIndicator.setComponent(POIUtils.extractValue(row.getCell(1), evaluator));
				currentIndicator.setCode(hackCode(POIUtils.extractValue(row.getCell(2), evaluator)));
				currentIndicator.setIndicator((POIUtils.extractValue(row.getCell(3), evaluator)));
				currentIndicator.setVariableDescription(POIUtils.extractValue(row.getCell(4), evaluator));
				currentIndicator.setCountryCoverage(POIUtils.extractValue(row.getCell(7), evaluator));
				currentIndicator.setTimeCoverage(POIUtils.extractValue(row.getCell(8), evaluator));
				currentIndicator.setHigh(POIUtils.extractValue(row.getCell(9), evaluator).equalsIgnoreCase("High")); //Keep String?
				currentIndicator.setType(PRIMARY);
				currentIndicator.setUriDataProvider(HTTP_WWW_WEBFOUNDATION_ORG);
				List<ProviderTO> providers = new LinkedList<ProviderTO>();
				providers.add(new ProviderTO(HTTP_WWW_WEBFOUNDATION_ORG,POIUtils.extractValue(row.getCell(5), evaluator)));
				currentIndicator.setSource(providers );	
				indicatorTOs.add(currentIndicator);
				System.out.println(currentIndicator);
			}		
		}
		return indicatorTOs;
	}

	private String hackCode(String extractValue) {
		return extractValue.toLowerCase().replaceAll("q", "Q");
	}
	private List<DatasetTO> loadSecondaryIndicators(HSSFWorkbook wb) {
		List<DatasetTO> indicatorTOs = new LinkedList<DatasetTO>();
		HSSFSheet sheet = wb.getSheet(SECONDARY_INDICATORS_SHEET_NAME);
		FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
		int rows = sheet.getPhysicalNumberOfRows();
		for (int r = 2; r < rows; r++) {
			HSSFRow row = sheet.getRow(r);
			if (row == null) {
				continue;
			}

			int cells = row.getPhysicalNumberOfCells();
			if(cells >= 17 && cells <= 19){
				DatasetTO currentIndicator = new DatasetTO();
				currentIndicator.setLayer(POIUtils.extractValue(row.getCell(0), evaluator));
				currentIndicator.setComponent(POIUtils.extractValue(row.getCell(1), evaluator));
				currentIndicator.setCode(POIUtils.extractValue(row.getCell(2), evaluator));
				currentIndicator.setIndicator((POIUtils.extractValue(row.getCell(3), evaluator)));
				currentIndicator.setVariableDescription(POIUtils.extractValue(row.getCell(4), evaluator));
				currentIndicator.setUriDataProvider(POIUtils.extractValue(row.getCell(6), evaluator));
				String []sources = POIUtils.extractValue(row.getCell(5), evaluator).split("/");
				List<ProviderTO> providers = new LinkedList<ProviderTO>();
				for(int i = 0; i<sources.length;i++){
					providers.add(
							new ProviderTO(
									POIUtils.extractValue(row.getCell(6), evaluator),
									sources[i]));	
				}
				currentIndicator.setSource(providers);									
				currentIndicator.setLatestReport((POIUtils.extractValue(row.getCell(7), evaluator)));
				for (int i = 8; i<11; i++){
					currentIndicator.getCriteria().add(POIUtils.extractValue(row.getCell(i), evaluator).equalsIgnoreCase("Yes"));							
				}
				currentIndicator.setCountryCoverage(POIUtils.extractValue(row.getCell(13), evaluator));
				currentIndicator.setTimeCoverage(POIUtils.extractValue(row.getCell(14), evaluator));
				currentIndicator.setDataRange(POIUtils.extractValue(row.getCell(15), evaluator));
				currentIndicator.setHigh(POIUtils.extractValue(row.getCell(16), evaluator).equalsIgnoreCase("High")); //Keep String?
				currentIndicator.setType(SECONDARY);	
				currentIndicator.setMissingNotes(POIUtils.extractValue(row.getCell(17), evaluator));
				indicatorTOs.add(currentIndicator);				
			}
		}


		return indicatorTOs;
	}

	public List<DatasetTO> getSecondaryIndicators() {
		return this.secondaryIndicatorTOs;
	}

	public List<DatasetTO> getPrimaryIndicators() {
		return this.primaryIndicatorTOs;
	}

}
